<?php

/**
 * This is the model class for table "tbl_customers".
 *
 * The followings are the available columns in table 'tbl_customers':
 * @property string $custno
 * @property string $lastname
 * @property string $firstname
 * @property string $add1
 * @property string $add2
 * @property string $city
 * @property string $state
 * @property string $zip
 * @property string $phone1
 * @property string $phone2
 * @property string $creditrate
 * @property string $terms
 * @property string $pricecode
 * @property double $discount
 * @property double $latecharge
 * @property string $custref
 * @property integer $statement
 * @property string $lastpaid
 * @property integer $requirepo
 * @property integer $post
 * @property string $phone3
 * @property string $phone4
 * @property string $lblphone1
 * @property string $lblphone2
 * @property string $lblphone3
 * @property string $lblphone4
 * @property string $sort
 * @property string $defpriority
 * @property string $salesperson
 * @property integer $customerinactive
 * @property string $dateadded
 * @property string $website
 * @property integer $emailstatement
 * @property string $notes
 * @property string $datemodified
 * @property string $image
 * @property string $lockby
 * @property string $locktime
 * @property string $quickbook
 * @property string $esc_account
 * @property string $fdic
 * @property string $aba
 * @property integer $abamember
 * @property integer $achsetup
 */
 
class Customers extends CActiveRecord
{
    public $file;

    /**
     * Returns the static model of the specified AR class.
     * @return Customers the static model class
     */
    public static function model($className = __CLASS__)
    {

        return parent::model($className);
    }

    /**
     * @return string the associated database table name
     */
    public function tableName()
    {
        return 'tbl_customers';
    }

    /**
     * @return array validation rules for model attributes.
     */
    public function rules()
    {
        // NOTE: you should only define rules for those attributes that
        // will receive user inputs.
        return array(
            array('custno, add1, city, state, zip, terms, pricecode,  requirepo, salesperson, customerinactive, emailstatement, abamember', 'required'),
            array('statement, achsetup, requirepo, post, customerinactive, emailstatement', 'numerical', 'integerOnly' => true),
            array('discount, latecharge', 'numerical'),
            array('creditrate, terms', 'length', 'max' => 7),
            array('custno', 'length', 'max' => 7, 'min' => 7),
            array('lastname, add1, add2', 'length', 'max' => 50),
            array('firstname', 'length', 'max' => 25),
            array('lockby,locktime,city', 'length', 'max' => 30),
            array('county', 'length', 'max' => 150),
            array('state', 'length', 'max' => 15),
            array('zip', 'length', 'max' => 10),
            array('phone1, phone2, phone3, phone4, lblphone1, lblphone2, lblphone3, lblphone4', 'length', 'max' => 12),
            array('pricecode', 'length', 'max' => 1),
            array('custref', 'length', 'max' => 15),
            array('sort, defpriority', 'length', 'max' => 6),
            array('salesperson', 'length', 'max' => 4),
            array('fdic,aba', 'length', 'max' => 255),
	    array('website', 'length', 'max' => 128),
            array('notes,specialnotes,quickbook', 'length', 'max' => 10000),
			array('esc_account','length','max'=>7),
            array('lastpaid, dateadded, datemodified,discount, latecharge,', 'safe'),
            //image section
            array('file', 'file', 'types' => 'jpg,png,jpeg,bmp,gif', 'maxSize' => 1000000, 'allowEmpty' => true),
            //check term
            array('terms', 'exist', 'className' => 'Terms', 'attributeName' => 'termid'),
            // The following rule is used by search().
            // Please remove those attributes that should not be searched.
            array('custno, lastname, firstname, add1, add2, city, state, zip, phone1, phone2, creditrate, terms, pricecode, discount, latecharge, custref, statement, lastpaid, requirepo, post, phone3, phone4, lblphone1, lblphone2, lblphone3, lblphone4, sort, defpriority, salesperson, customerinactive, dateadded, website, emailstatement, notes, datemodified', 'safe', 'on' => 'search'),
        );
    }

    /**
     * @return array relational rules.
     */
    public function relations()
    {
        // NOTE: you may need to adjust the relation name and the related
        // class name for the relations automatically generated below.
        return array(
            'term' => array(self::BELONGS_TO, 'Terms', 'terms'),
            'contractModelList'=>array(self::HAS_MANY,'Contracts','custno'),
            'serviceContractModelList'=>array(self::HAS_MANY,'Contracts','custno','condition'=>'contractype = 1'),
            'invoiceModelList'=>array(self::HAS_MANY,'Invoice','custno','condition'=>'invoicetype=:typeInvoice','params'=>array('typeInvoice'=>Invoice::TYPE_INVOICE),'order'=>'locno'),
            'salesOrderModelList'=>array(self::HAS_MANY,'Invoice','custno','condition'=>'invoicetype=:typeSalesOrder','params'=>array('typeSalesOrder'=>Invoice::TYPE_SALES_ORDERS),'order'=>'locno'),            
        );
    }
    public function getSalesOrderNotFullyInvoice(){
   
        $list = Invoice::model()->findAll(array(
            'alias'=>'i',
            'condition'=>'i.custno=:custno and i.invoicetype=:invoicetype 
                and i.amountcharge>(select sum(amountcharge) from tbl_invoice where salesordersorg=i.invoice)',
            'params'=>array(
                'custno'=>$this->custno,
                'invoicetype'=>Invoice::TYPE_SALES_ORDERS,    
            )
        ));
        return $list;
    }
    /**
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels()
    {
        return array(
            'custno' => 'Customer number',
            'lastname' => 'Last name / Company name',
            'firstname' => 'First name',
            'add1' => 'Address 1',
            'add2' => 'Address 2',
            'city' => 'City',
            'state' => 'State',
            'zip' => 'Zip',
            'phone1' => 'Phone 1',
            'phone2' => 'Phone 2',
            'creditrate' => 'Credit Rating',
            'terms' => 'Terms',
            'pricecode' => 'Price Code',
            'discount' => 'Discount',
            'latecharge' => 'Late Charge',
            'custref' => 'Custref',
            'statement' => 'Statement',
            'lastpaid' => 'Lastpaid',
            'requirepo' => 'Require PO',
            'post' => 'Post',
            'phone3' => 'Phone 3',
            'phone4' => 'Phone 4',
            'lblphone1' => 'Phone 1',
            'lblphone2' => 'Phone 2',
            'lblphone3' => 'Phone 3',
            'lblphone4' => 'Phone 4',
            'sort' => 'Sort',
            'fdic' => 'FDIC No',
	    'aba' => 'ABA No',
	    'abamember' => 'ABA Member',
	    'defpriority' => 'Defpriority',
            'salesperson' => 'Sales Person',
            'customerinactive' => 'Customer Status',
            'dateadded' => 'Dateadded',
            'website' => 'Website',
            'emailstatement' => 'Email Statements',
            'notes' => 'Notes',
            'datemodified' => 'Datemodified',
            'image' => 'Image',
            'file' => 'File',
            'lockby' => 'Lock by',
            'locktime' => 'Lock time',
            'quickbook'=>'QuickBooks Account',
            'esc_account'=>'ESC Account',
	    'achsetup'=>'ACH Setup'
        );
    }

    public function init()
    {
        parent::init();
        $this->custno = Counters::model()->getNextNumber('customer');

    }


    /*
    * Get all locations of an customer
    * @return CActionDataProvider
    */

    public function getLocationList($custno = NULL)
    {
        if ($custno == NULL) {
            $custno = $this->custno;
        }
        $list = Locations::model()->findAll('locationinactive=0 AND custno=:custno', array('custno' => $custno));
        $rs = array();
        if (count($list) > 0) {
            foreach ($list as $item) {
                $rs[$item->id] = $item->locno . ' - ' . $item->locname . ' | ' . $item->add1 . ' | ' . $item->city . ' | ' . $item->state;
            }
        }
        return $rs;
    }

    public function beforeValidate()
    {
        parent::beforeValidate();
        if ($this->isNewRecord) {
            return Counters::model()->saveNumber('customer');
        }
        return true;
    }

    public function beforeSave()
    {


        if ($this->isNewRecord) {
            $this->dateadded = DateUtil::getDefaultCurrentDateTime();
        }
        $this->datemodified = DateUtil::getDefaultCurrentDateTime();

        $this->file = CUploadedFile::getInstance($this, 'file');
        if ($this->file != NULL) {
            //made a random name base on timestamp
            $ext = getFileExt($this->file);
            $name = getFileBodyName($this->file);
            $this->image = $name . '_' . time() . $ext;
            $this->file->saveAs('images/content/' . $this->image, true);
        }


        return parent::beforeSave();

    }

    public function getActiveText()
    {
        return isset($this->activeList[$this->customerinactive]) ? $this->activeList[$this->customerinactive] : NULL;
    }

    public function getActiveList()
    {
        return array(
            '-1' => 'Pending',
            '0' => 'Active',
            '1' => 'Inactive',
        );
    }


    public function getTermList()
    {
        $terms = Terms::model()->findAll();
        $rs = array();
        if ($terms != NULL && count($terms) > 0) {

            foreach ($terms as $i => $term) {
                $rs[$term->termid] = $term->term;
            }

        }
        return $rs;
    }

    public function getTermText()
    {
        return isset($this->termList[$this->terms]) ? $this->termList[$this->terms] : NULL;
    }
    public function getNotFullyInvoicedSalesOrderModelList(){
        $list = Invoice::model()->findAll(array(
            'alias'=>'i1',
            'condition'=>"((select sum(i2.amountcharge) from tbl_invoice i2 where i2.salesordersorg = i1.invoice) < i1.amountcharge 
                                or not exists(select i3.* from tbl_invoice i3 where i3.salesordersorg = i1.invoice and i3.invoicetype=:invoicetype))
                            and custno=:custno and invoicetype=:type
                ",
            'params'=>array(
                'custno'=>$this->custno,
                'type'=>Invoice::TYPE_SALES_ORDERS,
                'invoicetype'=>Invoice::TYPE_INVOICE
            )
        ));
        return $list;
    }
 
    public function getSalePersonList()
    {
        $emplist = Yii::app()->db->createCommand('select * from tbl_employees where lower(dept)="sales"')->queryAll();
        $rs = array(
            '' => 'N/A'
        );
        if (count($emplist) > 0) {
            foreach ($emplist as $emp) {
                $rs[$emp['empno']] = $emp['firstname'] . ' ' . $emp['lastname'];
            }
        }
        return $rs;
    }

    public function getPriceCode()
    {
        return array(
            'A' => 'A',
            'B' => 'B',
            'C' => 'C'
        );
    }
    
    public function getInvoiceDepositList(){
        $list=Invoice::model()->findAll(array(
            'alias'=>'i',
            'join'=>'left join tbl_rec_led_apply a on a.number = i.invoice
                     left join tbl_rec_led r on a.ledgerentryid = r.entryid',
            'condition'=>'r.custno=:custno AND r.type=:type',
            'params'=>array(
                'custno'=>$this->custno,
                'type'=>RecLed::TYPE_DP
            )
        ));                
        return $list;
    }
    public function getTotalPaidPayment()
    {
        $paid = Yii::app()->db->createCommand('select sum(paid) from tbl_rec where custno=:custno')->bindValue('custno', $this->custno)->queryScalar();
        if ($paid !== false && $paid != NULL) {
            return $paid;
        }
        return 0;
    }

    public function getTotalUnpaidPayment()
    {
        $money = Yii::app()->db->createCommand('select sum(invamt - paid) from tbl_rec where custno=:custno')->bindValue('custno', $this->custno)->queryScalar();
        if ($money !== false && $money != NULL) {
            return $money;
        }
        return 0;
    }

    public function getTotalUninvoicedWork()
    {
        $salesOrderList = Invoice::model()->findAll('invoicetype=:type AND custno=:custno', array(
            'type' => Invoice::TYPE_SALES_ORDERS,
            'custno' => $this->custno,

        ));
        $rs = 0;
        /* @var $salesOrder Invoice */
        foreach ($salesOrderList as $salesOrder) {
            $invoice = Yii::app()->db->createCommand('select invoice from tbl_invoice where salesordersorg=:salesOrder')->bindValue('salesOrder', $salesOrder->invoice)->queryScalar();
            //if there is no invoice associate with this sales order yet
            if ($invoice === false || $invoice == NULL) {
                $rs += $salesOrder->amountcharge;
            }
        }
        return number_format($rs, 2);
    }
    public function getOpenInvoiceAmount(){
        $sum=Yii::app()->db->createCommand('select sum(invamt-paid) from tbl_rec where custno=:custno AND paidoff is NULL')
                ->bindValues(array('custno'=>$this->custno))->queryScalar();
        if($sum!==false && $sum!=NULL){
            return $sum;
        }
        return 0;
    }
    public function getOpenInvoiceList(){
        $list=Invoice::model()->findAll(array(
            'alias'=>'i',
            'join'=>'left join tbl_rec r on r.invoice=i.invoice',
            'condition'=>'i.custno=:custno AND r.paid=0 AND i.invoicetype=:type',
            'params'=>array(
                'custno'=>$this->custno,
                'type'=>Invoice::TYPE_INVOICE
            )
        ));
        return $list;
    }
    
    public function getTotalCredit(){
        $sum=Yii::app()->db->createCommand('select sum(amount) from tbl_rec_led where custno=:custno AND type=:type')
                ->bindValues(
                        array(
                            'custno'=>$this->custno,
                            'type'=>RecLed::TYPE_CREDIT
                ))->queryScalar();
        if($sum!==false && $sum!=NULL){
            return $sum;
        }
        return 0;
    }
    public function getInvoiceCreditList(){
        $list=Invoice::model()->findAll(array(
            'alias'=>'i',
            'join'=>'left join tbl_rec_led_apply a on a.number = i.invoice
                     left join tbl_rec_led r on a.ledgerentryid = r.entryid',
            'condition'=>'r.custno=:custno AND r.type=:type',
            'params'=>array(
                'custno'=>$this->custno,
                'type'=>RecLed::TYPE_CREDIT
            )
        ));                
        return $list;
    }
    public function getCreditMemoList(){
        $list=Invoice::model()->findAll('custno=:custno AND invoicetype=:type',array(
            'custno'=>$this->custno,
            'type'=>Invoice::TYPE_CREDIT_MEMO
        ));
        return $list;
    }
    public function getCreditMemoAmount(){
        $sum=Yii::app()->db->createCommand('select sum(amountcharge) from tbl_invoice where custno=:custno AND invoicetype=:type')
                ->bindValues(array(
                    'custno'=>$this->custno,
                    'type'=>Invoice::TYPE_CREDIT_MEMO
                ))
                ->queryScalar();
        if(is_numeric($sum)){
            return $sum;
        }else{
            return 0;
        }
    }
    public static function getCustomerName($custno){
        $row=Yii::app()->db->createCommand('select firstname,lastname from tbl_customers where custno=:custno')
                ->bindValues(array(
                    'custno'=>$custno
                ))
                ->queryRow();
        if($row!==false){
            return $row['firstname'].' '.$row['lastname'];
        }
        return '';
    }
    public function getTotalDeposit(){
        $totalDeposit = Yii::app()->db->createCommand('select sum(paid) from tbl_rec_led_apply where type=:type and custno=:custno')
                ->bindValues(array(
                    'type'=>RecLed::TYPE_DP,
                    'custno'=>$this->custno
                ))->queryScalar();
        if(is_numeric($totalDeposit)){
            return $totalDeposit;
        }
        return null;
    }
    public function getTotalSalesOrderAmount(){
        $total = Yii::app()->db->createCommand('select sum(amountcharge) from tbl_invoice where invoicetype=:type and custno=:custno')
                ->bindValues(array(
                    'type'=>Invoice::TYPE_SALES_ORDERS,
                    'custno'=>$this->custno
                ))->queryScalar();
        if(is_numeric($total)){
            return $total;
        }
        return null;
    }
    public function getTotalSalesOrderAmountNotInvoiced(){
        $total = Yii::app()->db->createCommand('select sum(i1.amountcharge) from tbl_invoice i1 
            where i1.invoicetype=:type and i1.custno=:custno 
            and not exists(select i2.* from tbl_invoice i2 where i2.salesordersorg = i1.invoice)')
                ->bindValues(array(
                    'type'=>Invoice::TYPE_SALES_ORDERS,
                    'custno'=>$this->custno
                ))
                ->queryScalar();
        if(is_numeric($total)){
            return $total;
        }
        return null;
    }
}
